﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;


namespace BLL.AlarmManager
{
    public class AlarmStatistics
    {
        OracleDAL.AlarmManager.AlarmStatistics alarm = new OracleDAL.AlarmManager.AlarmStatistics();

        /// <summary>
        /// 获得统计方式
        /// </summary>
        /// <returns></returns>
        public DataSet getStatisticsState()
        {
            return alarm.getStatisticsState();
        }

        /// <summary>
        /// 形成Table字符串（统计方式）
        /// </summary>
        /// <returns>TableStr</returns>
        public string buildStatisticsState()
        {
            string stateTable = "";
            DataSet ds = new DataSet();
            ds = getStatisticsState();
            if (ds.Tables[0] != null)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        if (ds.Tables[0].Rows[i]["ENAME"].ToString() == "TIME")
                        {
                            //形成表格统计方式相(时间)，及其单击方法 参数 this   
                            stateTable += "<td><input type =\"radio\" statisticsid=\"" + ds.Tables[0].Rows[i]["ID"] + "\" name =\"StState\" id=\"" + ds.Tables[0].Rows[i]["ENAME"] + "\"  value=\"" + ds.Tables[0].Rows[i]["ENAME"] + "\" onclick=\"showContentTime(this)\" />" + ds.Tables[0].Rows[i]["NAME"] + "</td>";
                        }
                        else
                        {
                            //形成表格统计方式相(非时间)，及其单击方法 参数 this  
                            stateTable += "<td><input type =\"radio\" statisticsid=\"" + ds.Tables[0].Rows[i]["ID"] + "\" tranid=\"" + ds.Tables[0].Rows[i]["TRANSID"].ToString().ToUpper() + "\"  transname=\"" + ds.Tables[0].Rows[i]["TRANSNAME"].ToString().ToUpper() + "\" transtable=\"" + ds.Tables[0].Rows[i]["TRANSTABLE"] + "\" codition=\"" + ds.Tables[0].Rows[i]["CODITIONSTR"] + "\" name =\"StState\" id=\"" + ds.Tables[0].Rows[i]["ENAME"] + "\"  value=\"" + ds.Tables[0].Rows[i]["ENAME"] + "\" onclick=\"showContent(this)\" />" + ds.Tables[0].Rows[i]["NAME"] + "</td>";
                        }

                    }
                }
            }
            if (stateTable != "")
            {
                stateTable = "<table id=\"tbStState\" cellpadding=\"0\" width =\"100%\" cellspacing=\"0\" ><tr><td style=\" background-color:#e9eef4;border-right:1px solid #93adc8;width:80px;\">统计方式：</td>" + stateTable + "</tr></table>";
            }
            return stateTable;
        }
        /// <summary>
        /// 获得字典表数据
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="table"></param>
        /// <param name="codition"></param>
        /// <returns></returns>
        public DataSet getCodeContent(string id, string name, string table, string codition)
        {
            return alarm.getStatisticsContent(id, name, table, codition);
        }
        /// <summary>
        /// 创建方案字符串SQL
        /// </summary>
        /// <param name="sqlStr">方案字符串SQL</param>
        /// <returns>执行结果</returns>
        public int excuteSql(string sqlStr)
        {
            return alarm.excuteSql(sqlStr);
        }
        /// <summary>
        ///形成统计方式条件table字符串（统计方式）
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="table"></param>
        /// <param name="codition"></param>
        /// <param name="isexit"></param>
        /// <returns></returns>
        public string getStatisticsContent(string id, string name, string table, string codition, string isexit)
        {
            DataSet ds = new DataSet();
            string contentTable = string.Empty;
            int count = 1;//跨行数
            ds = alarm.getStatisticsContent(id, name, table, codition);//获得配置信息
            if (ds.Tables[0] != null)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        if (i % 5 == 0)//大于5种类型换行
                        {
                            count++;
                            if (isexit.IndexOf(ds.Tables[0].Rows[i][id].ToString()) != -1)
                            {
                                contentTable += "</tr><tr><td><input type=\"checkbox\" checked=\"true\" value =\"" + ds.Tables[0].Rows[i][name] + "\" id =\"" + ds.Tables[0].Rows[i][id] + "\" /> " + ds.Tables[0].Rows[i][name] + "</td>";
                            }
                            else
                            {
                                contentTable += "</tr><tr><td><input type=\"checkbox\"  value =\"" + ds.Tables[0].Rows[i][name] + "\" id =\"" + ds.Tables[0].Rows[i][id] + "\" /> " + ds.Tables[0].Rows[i][name] + "</td>";
                            }

                        }
                        else
                        {
                            if (isexit.IndexOf(ds.Tables[0].Rows[i][id].ToString()) != -1)
                            {
                                contentTable += "<td><input type=\"checkbox\"  checked=\"true\" value =\"" + ds.Tables[0].Rows[i][name] + "\" id =\"" + ds.Tables[0].Rows[i][id] + "\" /> " + ds.Tables[0].Rows[i][name] + "</td>";
                            }
                            else
                            {
                                contentTable += "<td><input type=\"checkbox\"   value =\"" + ds.Tables[0].Rows[i][name] + "\" id =\"" + ds.Tables[0].Rows[i][id] + "\" /> " + ds.Tables[0].Rows[i][name] + "</td>";
                            }
                        }
                    }

                }
            }
            if (contentTable != "")
            {
                contentTable = "<table id=\"tbStContent\" cellpadding=\"0\" width =\"100%\" cellspacing=\"0\" ><tr><td rowspan=\"" + count + "\" style=\" background-color:#e9eef4;border-right:1px solid #93adc8;width:80px;\">统计方式内容陈列：</td>" + contentTable + "</tr></table>";//生成TABLE表格及其单元格事件
            }
            return contentTable;
        }
        /// <summary>
        /// 构造查询条件
        /// </summary>
        /// <returns>sqlWhere</returns>
        public string buildSql(string commWhere, string[] arr, string starttime, string endtime, string type, string stat)
        {
            /*
             * 修改说明:告警统计只统计告警的开始时间在提供的选择的时间段之间
             * 修 改 人:qfkong
             * 修改时间:2010.11.10 15:50
             */
            string strSql = "";

            if (commWhere == "")
            {
                commWhere = " and 1=1";
            }
            else { commWhere = " and " + commWhere; }
            if (type != "sum")
            {
                for (int i = 0; i < arr.Length; i++)
                {
                    if (i != arr.Length - 1)
                    {
                        strSql += "select ";
                        strSql += "sum((decode(sign(endtime";
                        strSql += " -to_date('" + endtime + "','yyyy-mm-dd hh24:mi:ss')";
                        strSql += "),-1,endtime,";
                        strSql += "to_date('" + endtime + "','yyyy-mm-dd hh24:mi:ss')";
                        strSql += ")";

                        strSql += "  -  ";
                        strSql += "decode(sign(starttime";
                        strSql += " -to_date('" + starttime + "','yyyy-mm-dd hh24:mi:ss')";
                        strSql += "),1,starttime,";
                        strSql += "to_date('" + starttime + "','yyyy-mm-dd hh24:mi:ss')";
                        strSql += ") )*3600*24)";
                        strSql += "  as statistic,'" + arr[i] + "' as d ";
                        //      strSql += "   from  alarm_info ";
                        strSql += "   from  alarm_history ";
                        //替换
                        //strSql += "where " + stat + "='" + arr[i] + "' " + commWhere + " and (endtime>=to_date('" + starttime + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<= to_date('" + endtime + "','yyyy-mm-dd hh24:mi:ss')";
                        strSql += "where " + stat + "='" + arr[i] + "' " + commWhere + " and starttime>=to_date('" + starttime + "','yyyy-mm-dd hh24:mi:ss') and starttime<= to_date('" + endtime + "','yyyy-mm-dd hh24:mi:ss')";
                        // strSql += "select sum(round((endtime-starttime)*12*60*60)) as statistic,'" + arr[i] + "' as d from alarm_info where " + stat + "='" + arr[i] + "' " + commWhere + " and starttime>=to_date('" + starttime + "','yyyy-mm-dd hh24:mi:ss') and endtime<= to_date('" + endtime + "','yyyy-mm-dd hh24:mi:ss')";

                        strSql += " union all ";
                    }
                    else
                    {
                        strSql += "select sum(";

                        strSql += "(decode(sign(endtime";
                        strSql += " -to_date('" + endtime + "','yyyy-mm-dd hh24:mi:ss')";
                        strSql += "),-1,endtime,";
                        strSql += "to_date('" + endtime + "','yyyy-mm-dd hh24:mi:ss')";
                        strSql += ")";

                        strSql += "  -  ";
                        strSql += "decode(sign(starttime";
                        strSql += " -to_date('" + starttime + "','yyyy-mm-dd hh24:mi:ss')";
                        strSql += "),1,starttime,";
                        strSql += "to_date('" + starttime + "','yyyy-mm-dd hh24:mi:ss')";
                        strSql += ") )*3600*24)";

                        strSql += "  as statistic,'" + arr[i] + "' as d ";
                        strSql += "   from  alarm_history ";
                        //替换
                        //strSql += "where " + stat + "='" + arr[i] + "' " + commWhere + " and (endtime>=to_date('" + starttime + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<= to_date('" + endtime + "','yyyy-mm-dd hh24:mi:ss')";
                        strSql += "where " + stat + "='" + arr[i] + "' " + commWhere + " and starttime>=to_date('" + starttime + "','yyyy-mm-dd hh24:mi:ss') and starttime<= to_date('" + endtime + "','yyyy-mm-dd hh24:mi:ss')";
                        //  strSql += "select sum(round((endtime-starttime)*12*60*60)) as statistic,'" + arr[i] + "' as d from alarm_info where " + stat + "='" + arr[i] + "' " + commWhere + " and starttime>=to_date('" + starttime + "','yyyy-mm-dd hh24:mi:ss') and endtime<=to_date('" + endtime + "','yyyy-mm-dd hh24:mi:ss')";
                    }
                }
            }
            else
            {
                for (int i = 0; i < arr.Length; i++)
                {
                    if (i != arr.Length - 1)
                    {
                        strSql += "select count(*) as statistic,'" + arr[i] + "' as d from alarm_history where " + stat + "='" + arr[i] + "' " + commWhere;
                        //替换
                        //strSql += " and (endtime>=to_date('" + starttime + "','yyyy-mm-dd hh24:mi:ss') or endtime is null )and starttime<= to_date('" + endtime + "','yyyy-mm-dd hh24:mi:ss')";
                        strSql += " and starttime>=to_date('" + starttime + "','yyyy-mm-dd hh24:mi:ss') and starttime<= to_date('" + endtime + "','yyyy-mm-dd hh24:mi:ss')";
                        strSql += " union all ";
                    }
                    else
                    {

                        strSql += "select count(*) as statistic,'" + arr[i] + "' as d from alarm_history where " + stat + "='" + arr[i] + "' " + commWhere;
                        //替换
                        //strSql += " and (endtime>=to_date('" + starttime + "','yyyy-mm-dd hh24:mi:ss') or endtime is null )and starttime<= to_date('" + endtime + "','yyyy-mm-dd hh24:mi:ss')";
                        strSql += " and starttime>=to_date('" + starttime + "','yyyy-mm-dd hh24:mi:ss') and starttime<= to_date('" + endtime + "','yyyy-mm-dd hh24:mi:ss')";
                    }
                }
            }
            return strSql;
        }
        /// <summary>
        /// 返回统计结果集
        /// </summary>
        /// <param name="commWhere"共用条件></param>
        /// <param name="value">统计指标(Day,value)(Month,value)(Year,value) 类型逗号链接值</param>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <param name="type">统计类型</param>
        /// <returns></returns>
        public DataSet getStatisticsResultByTime(string commWhere, string value, string starttime, string endtime, string type)
        {
            string strSql = buildSqlByTime(commWhere, value, starttime, endtime, type);

            return DBUtility.OracleHelper.Query(strSql);
        }
        public string buildSqlByTime(string commWhere, string value, string starttime, string endtime, string type)
        {
            if (commWhere == "")
            {
                commWhere = " 1=1";
            }
            else { ; }
            DateTime stime = Convert.ToDateTime(starttime);
            DateTime etime = Convert.ToDateTime(endtime);
            string strSql = "";

            string[] arrValue = value.Split(',');
            ArrayList arr = new ArrayList();
            bool judage = true;//判断时间
            int i = 1;//

            int temp = Convert.ToInt32(arrValue[0]); //记录间隔值
            int temp_instr = temp; //临时存储
            switch (arrValue[1])
            {
                case "DAY":
                    while (judage)
                    {

                        if (stime.AddDays(temp) < etime)
                        {
                            if (i == 1)
                            {
                                if (type != "sum")
                                {
                                    //strSql += "select sum(round((endtime-starttime)*12*60*60)) as statistic,'" + string.Format("{0:d}", stime) + "-" + string.Format("{0:d}", stime.AddDays(temp)) + "'as  d from alarm_info where ";
                                    //strSql += commWhere + " and starttime>=to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss') and endtime<= to_date('" + string.Format("{0:d}", stime.AddDays(temp)) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    //strSql += " union all ";
                                    strSql += "select ";

                                    strSql += "sum((decode(sign(endtime";
                                    strSql += " -to_date('" + string.Format("{0:d}", stime.AddDays(temp)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),-1,endtime,";
                                    strSql += "to_date('" + string.Format("{0:d}", stime.AddDays(temp)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ")";

                                    strSql += "  -  ";
                                    strSql += "decode(sign(starttime";
                                    strSql += " -to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),1,starttime,";
                                    strSql += "to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ") )*3600*24)";

                                    strSql += "  as statistic,'" + string.Format("{0:d}", stime) + "-" + string.Format("{0:d}", stime.AddDays(temp)) + "'as  d ";
                                    strSql += "   from  alarm_history ";
                                    //替换
                                    //strSql += "where  " + commWhere + " and (endtime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<= to_date('" + stime.AddDays(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += "where  " + commWhere + " and starttime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss')  and starttime<= to_date('" + stime.AddDays(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " union all ";

                                }
                                else
                                {
                                    strSql += "select count(*) as statistic,'" + string.Format("{0:d}", stime) + "-" + string.Format("{0:d}", stime.AddDays(temp)) + "'as  d from alarm_history where ";
                                    strSql += commWhere;
                                    //替换
                                    //strSql += " and (endtime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<= to_date('" + stime.AddDays(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " and starttime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss')  and starttime<= to_date('" + stime.AddDays(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " union all ";
                                }
                            }
                            else
                            {
                                if (type != "sum")
                                {
                                    //strSql += "select sum(round((endtime-starttime)*12*60*60)) as statistic,'" + string.Format("{0:d}", stime.AddDays(temp - temp_instr)) + "-" + string.Format("{0:d}", stime.AddDays(temp)) + "' as d from alarm_info  where ";
                                    //strSql += commWhere + " and starttime>=to_date('" + string.Format("{0:d}", stime.AddDays(temp - temp_instr)) + "','yyyy-mm-dd hh24:mi:ss') and endtime<=to_date('" + string.Format("{0:d}", stime.AddDays(temp)) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    //strSql += " union all ";

                                    strSql += "select ";

                                    strSql += "sum((decode(sign(endtime";
                                    strSql += " -to_date('" + string.Format("{0:d}", stime.AddDays(temp)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),-1,endtime,";
                                    strSql += "to_date('" + string.Format("{0:d}", stime.AddDays(temp)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ")";

                                    strSql += "  -  ";
                                    strSql += "decode(sign(starttime";
                                    strSql += " -to_date('" + string.Format("{0:d}", stime.AddDays(temp - temp_instr)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),1,starttime,";
                                    strSql += "to_date('" + string.Format("{0:d}", stime.AddDays(temp - temp_instr)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ") )*3600*24)";

                                    strSql += "  as statistic,'" + string.Format("{0:d}", stime.AddDays(temp - temp_instr)) + "-" + string.Format("{0:d}", stime.AddDays(temp)) + "'as  d ";
                                    strSql += "   from  alarm_history ";
                                    //替换
                                    //strSql += "where  " + commWhere + " and (endtime>=to_date('" + stime.AddDays(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and  starttime<=to_date('" + stime.AddDays(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += "where  " + commWhere + " and starttime>=to_date('" + stime.AddDays(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss')  and  starttime<=to_date('" + stime.AddDays(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " union all ";
                                }
                                else
                                {
                                    strSql += "select count(*) as statistic,'" + string.Format("{0:d}", stime.AddDays(temp - temp_instr)) + "-" + string.Format("{0:d}", stime.AddDays(temp)) + "' as d from alarm_history  where ";
                                    strSql += commWhere;
                                    //替换
                                    //strSql += " and (endtime>=to_date('" + stime.AddDays(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<=to_date('" + stime.AddDays(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " and starttime>=to_date('" + stime.AddDays(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss') and starttime<=to_date('" + stime.AddDays(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " union all ";
                                }
                            }

                        }
                        else
                        {
                            if (i == 1)
                            {
                                if (type != "sum")
                                {
                                    //strSql += "select sum(round((endtime-starttime)*12*60*60)) as statistic,'" + string.Format("{0:d}", stime) + "-" + string.Format("{0:d}", etime) + "'as d from alarm_info where ";
                                    //strSql += commWhere + " and starttime>=to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss') and endtime<= to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss') ";

                                    strSql += "select ";

                                    strSql += "sum((decode(sign(endtime";
                                    strSql += " -to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),-1,endtime,";
                                    strSql += "to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ")";

                                    strSql += "  -  ";
                                    strSql += "decode(sign(starttime";
                                    strSql += " -to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),1,starttime,";
                                    strSql += "to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ") )*3600*24)";

                                    strSql += "  as statistic,'" + string.Format("{0:d}", stime) + "-" + string.Format("{0:d}", etime) + "'as d ";
                                    strSql += "   from  alarm_history ";
                                    //替换
                                    //strSql += "where  " + commWhere + " and (endtime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<=to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += "where  " + commWhere + " and starttime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss')  and starttime<=to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                }
                                else
                                {
                                    strSql += "select count(*) as statistic,'" + string.Format("{0:d}", stime) + "-" + string.Format("{0:d}", etime) + "'as d from alarm_history where ";
                                    strSql += commWhere;
                                    //替换
                                    //strSql += " and (endtime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<= to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " and starttime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss')  and starttime<= to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                }
                            }
                            else
                            {
                                if (type != "sum")
                                {
                                    //strSql += "select sum(round((endtime-starttime)*12*60*60)) as statistic,'" + string.Format("{0:d}", stime.AddDays(temp - temp_instr)) + "-" + string.Format("{0:d}", etime) + "' as d from alarm_info where ";
                                    //strSql += commWhere + " and starttime>=to_date('" + string.Format("{0:d}", stime.AddDays(temp - temp_instr)) + "','yyyy-mm-dd hh24:mi:ss') and endtime<= to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss') ";


                                    strSql += "select ";

                                    strSql += "sum((decode(sign(endtime";
                                    strSql += " -to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),-1,endtime,";
                                    strSql += "to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ")";

                                    strSql += "  -  ";
                                    strSql += "decode(sign(starttime";
                                    strSql += " -to_date('" + string.Format("{0:d}", stime.AddDays(temp - temp_instr)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),1,starttime,";
                                    strSql += "to_date('" + string.Format("{0:d}", stime.AddDays(temp - temp_instr)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ") )*3600*24)";

                                    strSql += "  as statistic,'" + string.Format("{0:d}", stime.AddDays(temp - temp_instr)) + "-" + string.Format("{0:d}", etime) + "'as d ";
                                    strSql += "   from  alarm_history ";
                                    //替换
                                    //strSql += "where  " + commWhere + " and (endtime>=to_date('" + stime.AddDays(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<=to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += "where  " + commWhere + " and starttime>=to_date('" + stime.AddDays(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss')  and starttime<=to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";

                                }
                                else
                                {
                                    strSql += "select count(*) as statistic,'" + string.Format("{0:d}", stime.AddDays(temp - temp_instr)) + "-" + string.Format("{0:d}", etime) + "' as d from alarm_history where ";
                                    strSql += commWhere;
                                    //替换
                                    //strSql += " and (endtime>=to_date('" + stime.AddDays(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<= to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " and starttime>=to_date('" + stime.AddDays(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss')  and starttime<= to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                }

                            }
                            break;
                        }
                        i++;
                        temp = i * temp_instr;

                    }
                    break;
                case "MONTH":
                    while (judage)
                    {
                        if (stime.AddMonths(temp) < etime)
                        {

                            if (i == 1)
                            {
                                if (type != "sum")
                                {
                                    //strSql += "select sum(round((endtime-starttime)*12*60*60)) as statistic,'" + string.Format("{0:d}", stime) + "-" + string.Format("{0:d}", stime.AddMonths(temp)) + "'as  d from alarm_info where ";
                                    //strSql += commWhere + " and starttime>=to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss') and endtime<= to_date('" + string.Format("{0:d}", stime.AddMonths(temp)) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    //strSql += " union all ";

                                    strSql += "select ";

                                    strSql += "sum((decode(sign(endtime";
                                    strSql += " -to_date('" + string.Format("{0:d}", stime.AddMonths(temp)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),-1,endtime,";
                                    strSql += "to_date('" + string.Format("{0:d}", stime.AddMonths(temp)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ")";

                                    strSql += "  -  ";
                                    strSql += "decode(sign(starttime";
                                    strSql += " -to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),1,starttime,";
                                    strSql += "to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ") )*3600*24)";

                                    strSql += "  as statistic,'" + string.Format("{0:d}", stime) + "-" + string.Format("{0:d}", stime.AddMonths(temp)) + "'as  d  ";
                                    strSql += "   from  alarm_history ";
                                    //替换
                                    //strSql += "where  " + commWhere + " and (endtime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and  starttime<=to_date('" + stime.AddMonths(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += "where  " + commWhere + " and starttime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss') and  starttime<=to_date('" + stime.AddMonths(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " union all ";
                                }
                                else
                                {
                                    strSql += "select count(*) as statistic,'" + string.Format("{0:d}", stime) + "-" + string.Format("{0:d}", stime.AddMonths(temp)) + "'as  d from alarm_history where ";
                                    strSql += commWhere;
                                    //替换
                                    //strSql += " and (endtime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<= to_date('" + stime.AddMonths(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " and starttime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss')  and starttime<= to_date('" + stime.AddMonths(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " union all ";

                                }
                            }
                            else
                            {
                                if (type != "sum")
                                {
                                    //strSql += "select sum(round((endtime-starttime)*12*60*60)) as statistic,'" + string.Format("{0:d}", stime.AddMonths(temp - temp_instr)) + "-" + string.Format("{0:d}", stime.AddMonths(temp)) + "' as d from alarm_info  where ";
                                    //strSql += commWhere + " and starttime>=to_date('" + string.Format("{0:d}", stime.AddMonths(temp - temp_instr)) + "','yyyy-mm-dd hh24:mi:ss') and endtime<=to_date('" + string.Format("{0:d}", stime.AddMonths(temp)) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    //strSql += " union all ";


                                    strSql += "select ";

                                    strSql += "sum((decode(sign(endtime";
                                    strSql += " -to_date('" + string.Format("{0:d}", stime.AddMonths(temp)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),-1,endtime,";
                                    strSql += "to_date('" + string.Format("{0:d}", stime.AddMonths(temp)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ")";

                                    strSql += "  -  ";
                                    strSql += "decode(sign(starttime";
                                    strSql += " -to_date('" + string.Format("{0:d}", stime.AddMonths(temp - temp_instr)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),1,starttime,";
                                    strSql += "to_date('" + string.Format("{0:d}", stime.AddMonths(temp - temp_instr)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ") )*3600*24)";

                                    strSql += "  as statistic,'" + string.Format("{0:d}", stime.AddMonths(temp - temp_instr)) + "-" + string.Format("{0:d}", stime.AddMonths(temp)) + "'as  d  ";
                                    strSql += "   from  alarm_history ";
                                    //替换
                                    //strSql += "where  " + commWhere + " and (endtime>=to_date('" + stime.AddMonths(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and  starttime<=to_date('" + stime.AddMonths(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += "where  " + commWhere + " and starttime>=to_date('" + stime.AddMonths(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss')  and  starttime<=to_date('" + stime.AddMonths(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " union all ";


                                }
                                else
                                {
                                    strSql += "select count(*) as statistic,'" + string.Format("{0:d}", stime.AddMonths(temp - temp_instr)) + "-" + string.Format("{0:d}", stime.AddMonths(temp)) + "' as d from alarm_history  where ";
                                    strSql += commWhere;
                                    //替换
                                    //strSql += " and (endtime>=to_date('" + stime.AddMonths(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<=to_date('" + stime.AddMonths(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " and starttime>=to_date('" + stime.AddMonths(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss')  and starttime<=to_date('" + stime.AddMonths(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " union all ";
                                }
                            }

                        }
                        else
                        {
                            if (i == 1)
                            {
                                if (type != "sum")
                                {
                                    //strSql += "select sum(round((endtime-starttime)*12*60*60)) as statistic,'" + string.Format("{0:d}", stime) + "-" + string.Format("{0:d}", etime) + "'as d from alarm_info where ";
                                    //strSql += commWhere + " and starttime>=to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss') and endtime<= to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss') ";


                                    strSql += "select ";

                                    strSql += "sum((decode(sign(endtime";
                                    strSql += " -to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),-1,endtime,";
                                    strSql += "to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ")";

                                    strSql += "  -  ";
                                    strSql += "decode(sign(starttime";
                                    strSql += " -to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),1,starttime,";
                                    strSql += "to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ") )*3600*24)";

                                    strSql += "  as statistic,'" + string.Format("{0:d}", stime) + "-" + string.Format("{0:d}", etime) + "'as  d  ";
                                    strSql += "   from  alarm_history ";
                                    //替换
                                    // strSql += "where  " + commWhere + " and (endtime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and  starttime<=to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += "where  " + commWhere + " and starttime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss')  and  starttime<=to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";

                                }
                                else
                                {

                                    strSql += "select count(*) as statistic,'" + string.Format("{0:d}", stime) + "-" + string.Format("{0:d}", etime) + "'as d from alarm_history where ";
                                    strSql += commWhere;
                                    // strSql += " and (endtime>=to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<= to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    //替换
                                    //strSql += " and (endtime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<= to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " and starttime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss') and starttime<= to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                }
                            }
                            else
                            {
                                if (type != "sum")
                                {
                                    //strSql += "select sum(round((endtime-starttime)*12*60*60)) as statistic,'" + string.Format("{0:d}", stime.AddMonths(temp - temp_instr)) + "-" + string.Format("{0:d}", etime) + "' as d from alarm_info where ";
                                    //strSql += commWhere + " and starttime>=to_date('" + string.Format("{0:d}", stime.AddMonths(temp - temp_instr)) + "','yyyy-mm-dd hh24:mi:ss') and endtime<= to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss') ";


                                    strSql += "select ";

                                    strSql += "sum((decode(sign(endtime";
                                    strSql += " -to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),-1,endtime,";
                                    strSql += "to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ")";

                                    strSql += "  -  ";
                                    strSql += "decode(sign(starttime";
                                    strSql += " -to_date('" + string.Format("{0:d}", stime.AddMonths(temp - temp_instr)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),1,starttime,";
                                    strSql += "to_date('" + string.Format("{0:d}", stime.AddMonths(temp - temp_instr)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ") )*3600*24)";

                                    strSql += "  as statistic,'" + string.Format("{0:d}", stime.AddMonths(temp - temp_instr)) + "-" + string.Format("{0:d}", etime) + "'as  d  ";
                                    strSql += "   from  alarm_history ";
                                    //替换
                                    //strSql += "where  " + commWhere + " and (endtime>=to_date('" + stime.AddMonths(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and  starttime<=to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += "where  " + commWhere + " and starttime>=to_date('" + stime.AddMonths(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss') and  starttime<=to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";

                                }
                                else
                                {
                                    strSql += "select count(*) as statistic,'" + string.Format("{0:d}", stime.AddMonths(temp - temp_instr)) + "-" + string.Format("{0:d}", etime) + "' as d from alarm_history where ";
                                    strSql += commWhere;
                                    //替换
                                    //strSql += " and (endtime>=to_date('" + stime.AddMonths(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<= to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " and starttime>=to_date('" + stime.AddMonths(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss')  and starttime<= to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                }
                            }
                            break;
                        }
                        i++;
                        temp = i * temp_instr;
                    }
                    break;
                case "YEAR":
                    while (judage)
                    {
                        if (stime.AddYears(temp) < etime)
                        {
                            if (i == 1)
                            {
                                if (type != "sum")
                                {
                                    //strSql += "select sum(round((endtime-starttime)*12*60*60)) as statistic,'" + string.Format("{0:d}", stime) + "-" + string.Format("{0:d}", stime.AddYears(temp)) + "'as  d from alarm_info where ";
                                    //strSql += commWhere + " and starttime>=to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss') and endtime<= to_date('" + string.Format("{0:d}", stime.AddYears(temp)) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    //strSql += " union all ";


                                    strSql += "select ";

                                    strSql += "sum((decode(sign(endtime";
                                    strSql += " -to_date('" + string.Format("{0:d}", stime.AddYears(temp)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),-1,endtime,";
                                    strSql += "to_date('" + string.Format("{0:d}", stime.AddYears(temp)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ")";

                                    strSql += "  -  ";
                                    strSql += "decode(sign(starttime";
                                    strSql += " -to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),1,starttime,";
                                    strSql += "to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ") )*3600*24)";

                                    strSql += "  as statistic,'" + string.Format("{0:d}", stime) + "-" + string.Format("{0:d}", stime.AddYears(temp)) + "'as  d  ";
                                    strSql += "   from  alarm_history ";
                                    //替换
                                    //strSql += "where  " + commWhere + " and (endtime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and  starttime<=to_date('" + stime.AddYears(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += "where  " + commWhere + " and starttime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss') and  starttime<=to_date('" + stime.AddYears(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " union all ";


                                }
                                else
                                {
                                    strSql += "select count(*) as statistic,'" + string.Format("{0:d}", stime) + "-" + string.Format("{0:d}", stime.AddYears(temp)) + "'as  d from alarm_history where ";
                                    strSql += commWhere;
                                    //替换
                                    //strSql += " and (endtime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<= to_date('" + stime.AddYears(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " and starttime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss')  and starttime<= to_date('" + stime.AddYears(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " union all ";
                                }
                            }
                            else
                            {
                                if (type != "sum")
                                {
                                    //strSql += "select sum(round((endtime-starttime)*12*60*60)) as statistic,'" + string.Format("{0:d}", stime.AddYears(temp - temp_instr)) + "-" + string.Format("{0:d}", stime.AddYears(temp)) + "' as d from alarm_info  where ";
                                    //strSql += commWhere + " and starttime>=to_date('" + string.Format("{0:d}", stime.AddYears(temp - temp_instr)) + "','yyyy-mm-dd hh24:mi:ss') and endtime<=to_date('" + string.Format("{0:d}", stime.AddYears(temp)) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    //strSql += " union all ";


                                    strSql += "select ";

                                    strSql += "sum((decode(sign(endtime";
                                    strSql += " -to_date('" + string.Format("{0:d}", stime.AddYears(temp)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),-1,endtime,";
                                    strSql += "to_date('" + string.Format("{0:d}", stime.AddYears(temp)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ")";

                                    strSql += "  -  ";
                                    strSql += "decode(sign(starttime";
                                    strSql += " -to_date('" + string.Format("{0:d}", stime.AddYears(temp - temp_instr)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),1,starttime,";
                                    strSql += "to_date('" + string.Format("{0:d}", stime.AddYears(temp - temp_instr)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ") )*3600*24)";

                                    strSql += "  as statistic,'" + string.Format("{0:d}", stime.AddYears(temp - temp_instr)) + "-" + string.Format("{0:d}", stime.AddYears(temp)) + "'as  d  ";
                                    strSql += "   from  alarm_history ";
                                    //替换
                                    //strSql += "where  " + commWhere + " and (endtime>=to_date('" + stime.AddYears(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and  starttime<=to_date('" + stime.AddYears(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += "where  " + commWhere + " and starttime>=to_date('" + stime.AddYears(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss')  and  starttime<=to_date('" + stime.AddYears(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " union all ";
                                }
                                else
                                {
                                    strSql += "select count(*) as statistic,'" + string.Format("{0:d}", stime.AddYears(temp - temp_instr)) + "-" + string.Format("{0:d}", stime.AddYears(temp)) + "' as d from alarm_history  where ";
                                    strSql += commWhere;
                                    //替换
                                    //strSql += " and (endtime>=to_date('" + stime.AddYears(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<=to_date('" + stime.AddYears(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " and starttime>=to_date('" + stime.AddYears(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss')  and starttime<=to_date('" + stime.AddYears(temp) + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " union all ";
                                }
                            }
                        }
                        else
                        {
                            if (i == 1)
                            {
                                if (type != "sum")
                                {
                                    //strSql += "select sum(round((endtime-starttime)*12*60*60)) as statistic,'" + string.Format("{0:d}", stime) + "-" + string.Format("{0:d}", etime) + "'as d from alarm_info where ";
                                    //strSql += commWhere + " and starttime>=to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss') and endtime<= to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss') ";

                                    strSql += "select ";

                                    strSql += "sum((decode(sign(endtime";
                                    strSql += " -to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),-1,endtime,";
                                    strSql += "to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ")";

                                    strSql += "  -  ";
                                    strSql += "decode(sign(starttime";
                                    strSql += " -to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),1,starttime,";
                                    strSql += "to_date('" + string.Format("{0:d}", stime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ") )*3600*24)";

                                    strSql += "  as statistic,'" + string.Format("{0:d}", stime) + "-" + string.Format("{0:d}", etime) + "'as  d  ";
                                    strSql += "   from  alarm_history ";
                                    //替换
                                    //strSql += "where  " + commWhere + " and (endtime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and  starttime<=to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += "where  " + commWhere + " and starttime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss')  and  starttime<=to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";

                                }
                                else
                                {
                                    strSql += "select count(*) as statistic,'" + string.Format("{0:d}", stime) + "-" + string.Format("{0:d}", etime) + "'as d from alarm_history where ";
                                    strSql += commWhere;
                                    //替换
                                    // strSql += " and (endtime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<= to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " and starttime>=to_date('" + stime + "','yyyy-mm-dd hh24:mi:ss')  and starttime<= to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                }
                            }
                            else
                            {
                                if (type != "sum")
                                {
                                    //strSql += "select sum(round((endtime-starttime)*12*60*60)) as statistic,'" + string.Format("{0:d}", stime.AddYears(temp - temp_instr)) + "-" + string.Format("{0:d}", etime) + "' as d from alarm_info where ";
                                    //strSql += commWhere + " and starttime>=to_date('" + string.Format("{0:d}", stime.AddYears(temp - temp_instr)) + "','yyyy-mm-dd hh24:mi:ss') and endtime<= to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss') ";


                                    strSql += "select ";

                                    strSql += "sum((decode(sign(endtime";
                                    strSql += " -to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),-1,endtime,";
                                    strSql += "to_date('" + string.Format("{0:d}", etime) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ")";

                                    strSql += "  -  ";
                                    strSql += "decode(sign(starttime";
                                    strSql += " -to_date('" + string.Format("{0:d}", stime.AddYears(temp - temp_instr)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += "),1,starttime,";
                                    strSql += "to_date('" + string.Format("{0:d}", stime.AddYears(temp - temp_instr)) + "','yyyy-mm-dd hh24:mi:ss')";
                                    strSql += ") )*3600*24)";

                                    strSql += "  as statistic,'" + string.Format("{0:d}", stime.AddYears(temp - temp_instr)) + "-" + string.Format("{0:d}", etime) + "'as  d  ";
                                    strSql += "   from  alarm_history ";
                                    //替换
                                    //strSql += "where  " + commWhere + " and (endtime>=to_date('" + stime.AddYears(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and  starttime<=to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += "where  " + commWhere + " and starttime>=to_date('" + stime.AddYears(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss') and  starttime<=to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                }
                                else
                                {
                                    strSql += "select count(*) as statistic,'" + string.Format("{0:d}", stime.AddYears(temp - temp_instr)) + "-" + string.Format("{0:d}", etime) + "' as d from alarm_history where ";
                                    strSql += commWhere;
                                    //替换
                                    //strSql += " and (endtime>=to_date('" + stime.AddYears(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<= to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                    strSql += " and starttime>=to_date('" + stime.AddYears(temp - temp_instr) + "','yyyy-mm-dd hh24:mi:ss')  and starttime<= to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') ";
                                }
                            }
                            break;
                        }
                        i++;
                        temp = i * temp_instr;
                    }
                    break;
                default:
                    break;
            }

            return strSql;
        }

        /// <summary>
        /// 返回统计结果集
        /// </summary>
        /// <param name="commWhere">共用条件</param>
        /// <param name="arr">统计指标</param>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <param name="type">统计方式：时长（sum）、次数(count)</param>
        /// <returns></returns>
        public DataSet getStatisticsResult(string commWhere, string[] arr, string starttime, string endtime, string type, string stat)
        {

            string whereSql = buildSql(commWhere, arr, starttime, endtime, type, stat);
            return alarm.getDs(whereSql);
        }
        /// <summary>
        /// 获得方案数据
        /// </summary>
        /// <returns></returns>
        public DataSet getScheme()
        {
            string strSql = "select schemeid,schemename from alarm_scheme";
            return alarm.getDs(strSql);
        }
        /// <summary>
        /// 根据方案的统计项目中STATISTICITEM来获取统计方式（ALARM_STATISTICS中的ENAME匹配）
        /// </summary>
        /// <param name="ename"></param>
        /// <returns></returns>
        public DataSet getStatisticStat(string id)
        {
            string strSql = "select ID,NAME,ENAME,AREASTATE,LEVELCODE,CODITIONSTR,TRANSID,TRANSNAME,TRANSTABLE from alarm_statistics where  id='" + id + "'";
            return alarm.getDs(strSql);
        }
        /// <summary>
        /// 获取方案信息根据方案ID和状态
        /// </summary>
        /// <param name="schemeid"></param>
        /// <returns></returns>
        public DataSet getSchemeItem(string schemeid, string conditionstate)
        {
            string strSql = "select SCHEMEID,STAISTICSITEM,CONDITIONID,CONDITIONVALUE,CONDITIONSTATE from alarm_scheme_item where SCHEMEID='" + schemeid + "' and CONDITIONSTATE='" + conditionstate + "' ";
            return alarm.getDs(strSql);
        }
        /// <summary>
        /// 获取方案信息根据方案ID
        /// </summary>
        /// <param name="schemeid"></param>
        /// <returns></returns>
        public DataSet getScheme(string schemeid)
        {
            string strSql = "select SCHEMEID,STAISTICSITEM,CONDITIONID,CONDITIONVALUE,CONDITIONSTATE from alarm_scheme_item where SCHEMEID='" + schemeid + "'";
            return alarm.getDs(strSql);
        }
    }

}
